-- lists all user-object permissions. filterable by user
SET IMPLICIT_TRANSACTIONS OFF
DECLARE @permission TABLE (action TINYINT NOT Null, permission VARCHAR(32))
SET NOCOUNT ON
INSERT INTO @permission VALUES ( 26, 'REFERENCES') 
INSERT INTO @permission VALUES (178, 'CREATE FUNCTION')
INSERT INTO @permission VALUES (193, 'SELECT')
INSERT INTO @permission VALUES (195, 'INSERT')
INSERT INTO @permission VALUES (196, 'DELETE')
INSERT INTO @permission VALUES (197, 'UPDATE')
INSERT INTO @permission VALUES (198, 'CREATE TABLE')
INSERT INTO @permission VALUES (203, 'CREATE DATABASE')
INSERT INTO @permission VALUES (207, 'CREATE VIEW')
INSERT INTO @permission VALUES (222, 'CREATE PROCEDURE')
INSERT INTO @permission VALUES (224, 'EXECUTE')
INSERT INTO @permission VALUES (228, 'BACKUP DATABASE')
INSERT INTO @permission VALUES (233, 'CREATE DEFAULT')
INSERT INTO @permission VALUES (235, 'BACKUP LOG')
INSERT INTO @permission VALUES (236, 'CREATE RULE')
SET NOCOUNT OFF
SELECT
	[Principal Name]= grantee_principal.name,
	[Database]	= DB_NAME(),
	[Schema]	= USER_NAME(obj.uid),
	[Object Name]	= OBJECT_NAME(obj.id),
	[Object Type]	= obj.type,
	[Protect Type]	= p.permission,
	[Protect Action]= CASE prmssn.protecttype WHEN 204 THEN 'GRANT_W_GRANT' WHEN 205 THEN 'GRANT' WHEN 206 THEN 'DENY' ELSE CAST(prmssn.protecttype AS VARCHAR(3)) END
  FROM dbo.sysprotects AS prmssn
	INNER JOIN dbo.sysobjects AS obj ON obj.id = prmssn.id
	INNER JOIN sysusers AS grantee_principal ON grantee_principal.uid = prmssn.uid
	INNER JOIN @permission p ON (prmssn.action = p.action)
--WHERE grantee_principal.name=N''
  ORDER BY [Principal Name], [Schema], [Object Name]
GO